Computers, and in particular, computer database applications, are used by businesses and other organizations to monitor and record information about an organization's activities. Often, the organization will have various processes or activities that must be performed, and which recur frequently. Indeed, it is common for an organization to have numerous instances of an activity in various stages of completion at any given time. As one example, a business may sell goods based on orders received from customers. An activity of interest may be fulfilling those customer orders; each purchase order represents a separate instance of that activity. At any particular time, that business may have multiple instances of the activity (i.e., multiple orders from multiple customers) in various stages of completion. As another example, a financial institution may loan funds to customers based on applications from those customers. An activity of interest may be the processing of a loan application to completion (e.g., approval or rejection), with each loan application representing a separate instance of the activity. At any particular time, there may be multiple loan application instances in various stages of processing. As yet another example, a governmental entity responsible for issuing permits may have multiple permit applications in various stages of being processed.
In order to monitor numerous instances of an activity, many organizations store information about those activity instances in a database program. In particular, a record or other data object can be created for each instance of the activity. A separate field or other component of the record is then established to hold a value for some type of information common to each instance. Using one of the previous examples as an illustration, a business selling goods may create a separate database record for each customer order. Within that record may be separate fields for the time the order was received, where the order was received, what was ordered, when the order was shipped, etc. Such use of a database program is often conceptualized as a table. Each instance of the activity is assigned a separate row (or tuple) of the table. Each type of information common to multiple instances is then assigned a separate column of the table.
In many businesses and other organizations, an activity instance database receives input from multiple application programs. An instance may be partially processed by a first application, and the results of that processing stored in the database. The instance may then be further processed by subsequent applications, with each application storing additional information in the database. These applications may not be configured to manipulate activity instance data in a consistent (or even compatible) manner. Indeed, one or more applications may not be aware that another application exists. FIGS. 1-3 provide a more detailed illustration of this problem, and also provide an example to build upon in the subsequent Detailed Description of the Preferred Embodiments.
FIG. 1 is a flow chart showing processing of customer purchase orders by a hypothetical wholesale business. For convenience, the business will be referred to herein as “Business A.” At block 10, Business A receives a purchase order and creates a database record for the purchase order; the time of order receipt is also entered. At block 12, additional data is input for record fields corresponding to quantity of product ordered and the purchaser's city. At block 16 a decision is made regarding whether the purchase order will be accepted. If the order is denied, an appropriate field of the record is populated at block 18 and the time of denial recorded. If the purchase order is approved, the approval is noted and a sales order number is assigned (block 20). When the order is packed for shipment, the package weight (PkgWeight) is recorded at block 22. When the order is shipped (block 24), an additional field is populated with the time of shipment. When the order is delivered, the time of delivery is input (block 26).
FIG. 2 is a table representing a portion of the database for purchase order instances of Business A. Each order is on a separate row, and each column corresponds to a type of data for an order. A row in the table of FIG. 2 is created for each instance of the activity at issue (in this case, a purchase order). Certain fields contain NULL values, indicating (in this example) that the value for a particular event is unknown because it has not yet transpired as to that particular purchase order. When a new purchase order is received, a new row is created and some of the fields set to non-null values (e.g., RecvTime, City, Quantity). If the purchase order is later approved and shipped, the package weight (PkgWeight) and ship time (ShipTime) are set to non-null values. When the shipment is received and confirmed, Delivery/Time is set to a non-null value. Process queries against the table can be readily created using well-known SQL (structured query language) coding techniques. A typical query of this database might be “which purchase orders received before 9:00 a.m. have not yet been delivered?” Appendix A shows an implementation of SQL code to create the table of FIG. 2 (“create table PO_InstanceData”) and a stored procedure to update the rows of the table in FIG. 2 (“create procedure PO_PrimaryImport”).
A stored procedure such as in Appendix A is satisfactory when the purchase order, sales and shipping data is generated from the same program. Unfortunately, this is often not the case. Instead, different parts of a business process are often affected by multiple applications. As shown in FIG. 3, the sales department of Business A uses one application (“application x”) to receive and process purchase orders. The shipping department of business A uses another application program (application y) for generating shipping data. The actual delivery of ordered goods is tracked and reported by another application which interfaces with a third party carrier (application z). Each of applications x-z is unaware of the other applications, and has no way to correlate its processing of an instance with processing performed by the other applications.
One solution is to attach a unique identifier to each instance and for each application to then associate data for the instance with that identifier. For example, the purchase order number could be used by all three applications. However, this solution is not practical in many cases. An activity instance may be processed by multiple unrelated organizations (e.g., Business A and the third party carrier of FIG. 3). These different organizations may be unwilling or unable to use the same software or to modify their separate software to track an activity instance using a common identifier. The same problem can occur within a single organization (e.g., the sales and shipping departments of Business A). For example, the shipping department of Business A may be using a legacy application for which modification or replacement would be prohibitively expensive. The example of FIG. 3 is also relatively simple, as only three applications are shown. As more organizations and software applications are added to the processing of an instance, tracking the instance using a global identifier becomes even more problematic.